﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EARS.App_Code
{
    public class MatricNoDBDataClass
    {
        public List<string> checkMatricNo()
        {
            List<string> listOfMatricNo = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select MatricNo from MatricNoDB";
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        listOfMatricNo.Add(dr["MatricNo"].ToString());
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return listOfMatricNo;
        }

        public string GetYear(string matricNo)
        {
            string year = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select YearofStudy from MatricNoDB where MatricNo = @MatricNo";
                comm.Parameters.AddWithValue("@MatricNo", matricNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    year = dr["YearofStudy"].ToString();
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return year;
        }
    }
}